//-----------------------------------------------------------------------------
//クラス名：SupplementaryClasses.java
//所    属：H3A1 04
//名    前：大瀬碩
//説    明：補講申請を保存する
//-----------------------------------------------------------------------------
package supplementary_classes;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Properties;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;


//@WebServlet("/SupplementaryClasses")
public class SupplementaryClasses extends HttpServlet {
	private static final long serialVersionUID = 1L;
       

    public SupplementaryClasses() {
        super();
    }


	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
	}


	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html; charset=UTF-8");
		request.setCharacterEncoding("UTF-8");
		
		HttpSession session = request.getSession(true);
		
		//DB接続
		ServletContext context = this.getServletContext();
		String path = context.getRealPath("/WEB-INF/connect.properties");
		
		InputStream in = new FileInputStream(path);
		Properties properties = new Properties();
		properties.load(in);
		in.close();
	 	
	    Connection conn = null;
	    String url = properties.getProperty("key1");
	    String user = properties.getProperty("key2");
	    String password = properties.getProperty("key3");
	    
	    Calendar cal1 = Calendar.getInstance();
	    int year = cal1.get(Calendar.YEAR);
	    int month = cal1.get(Calendar.MONTH) + 1;
	    int day = cal1.get(Calendar.DATE);
	    
	    
	    Object id = session.getAttribute("id");
	    Object class_id = session.getAttribute("class");
	    
	    Object str = session.getAttribute("str");
	    Object etr = session.getAttribute("etr");
	    
	    String scact = request.getParameter("scact");
	    scact = htmlspecialchars(scact);
	    int lecturer_id = 0;
	    
	    try {
		      Class.forName("com.mysql.jdbc.Driver").newInstance();
		      conn = DriverManager.getConnection(url, user, password);
		      
		      Statement stmt = conn.createStatement();
		      
		      //講師IDを取得
		      String sql = "SELECT * FROM lecturer WHERE charge_class1 = "+class_id+" OR charge_class2 = "+class_id+" OR charge_class3 = "+class_id;
		      
		      ResultSet rs = stmt.executeQuery(sql);
		      while(rs.next()){
		    	  lecturer_id = rs.getInt("lecturer_id");
		      }
		      
		      //DBに保存する
		      sql = "INSERT INTO makeup_class(student_id, date_and_supplementary, lecturer_id, start_time, end_time, content, class_id)"+
		      " VALUES(?, ?, ?, ?, ?, ?, ?)";
		      
		      
		      PreparedStatement pstmt = conn.prepareStatement(sql);
		      
		      pstmt.setObject(1, id);
		      pstmt.setObject(2, year+"/"+month+"/"+day);
		      pstmt.setObject(3, lecturer_id);
		      pstmt.setObject(4, str);
		      pstmt.setObject(5, etr);
		      pstmt.setObject(6, scact);
		      pstmt.setObject(7, class_id);
		      int num = pstmt.executeUpdate();
		      
	    }catch (ClassNotFoundException e){
	    	log("ClassNotFoundException:" + e.getMessage());
	    }catch (SQLException e){
	    	log("SQLException:" + e.getMessage());
	    }catch (Exception e){
	    	log("Exception:" + e.getMessage());
	    }finally{
	      try{
	        if (conn != null){
	          conn.close();
	        }
	      }catch (SQLException e){
	        
	      }
	    }
	    
	    //セッション破棄
	    session.removeAttribute("stbtn");
		session.removeAttribute("etbtn");
		session.removeAttribute("scact");
		session.removeAttribute("actedit");
	    RequestDispatcher rd = request.getRequestDispatcher("Completion(SupplementaryClasses).jsp");
		rd.forward(request,response);
	}
	
	public String htmlspecialchars ( String str )
	{
	String ret_val = new String(str);

	String[] escape = {"&", "<", ">", "\"", "\'", "\n", "\t"};
	String[] replace = {"&amp;", "&lt;", "&gt;", "&quot;", "&#39;", "<br>", "&#x0009;"};

	for ( int i=0; i < escape.length; i++ )
	{
	      ret_val = ret_val.replace(escape[i], replace[i]);
	}

	return ret_val;
	}

}